package dao.dao;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import dao.bean.User;

import javax.sql.DataSource;
import java.beans.PropertyVetoException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDaoJdbcC3P0 implements UserDao {

    private final DataSource dataSource;
    {
        try {
            ComboPooledDataSource cpds = new ComboPooledDataSource();
            cpds.setDriverClass("com.mysql.jdbc.Driver");
            cpds.setJdbcUrl("jdbc:mysql://localhost:3306/production_eshop");
            cpds.setUser("username");
            cpds.setPassword("password");
            
            cpds.setOverrideDefaultUser("root");
            cpds.setOverrideDefaultPassword("2479");

//            cpds.setMaxStatements(180);
//            cpds.setMaxStatementsPerConnection(10);
//
//            cpds.setMinPoolSize(1);
//            cpds.setAcquireIncrement(1);
//            cpds.setMaxPoolSize(20);

            this.dataSource = cpds;
        } catch (PropertyVetoException e) {
            throw new RuntimeException("Exception during configuring c3p0", e);
        }
    }

    private Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    @Override
    public List<User> selectAll() throws SQLException {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
            conn.setAutoCommit(false);
            ps = conn.prepareStatement("SELECT id, login FROM User");
            rs = ps.executeQuery();
            ArrayList<User> result = new ArrayList<User>();
            while (rs.next()) {
                int id = rs.getInt("id");
                String login = rs.getString("login");
                User user = new User(id);
                user.setLogin(login);
                result.add(user);
            }
            conn.commit();
            return result;
        } catch (SQLException e) {
            JdbcUtils.rollbackQuietly(conn);
            throw e;
        } finally {
            JdbcUtils.closeQuietly(rs);
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
    }

    @Override
    public int deleteById(int id) throws SQLException {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement("DELETE FROM User WHERE id = ?");
            ps.setInt(1, id);
            return ps.executeUpdate();
        } finally {
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
    }

    @Override
    public void insert(User user) throws SQLException {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement("INSERT INTO User (login) VALUES (?)");
            ps.setString(1, user.getLogin());
            ps.executeUpdate();
        } finally {
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
    }

    @Override
    public void shutdown() {
        // NOP
    }
}
